package com.liu.mysqldemo.mapper;

import com.liu.mysqldemo.entity.Person;
import org.apache.ibatis.annotations.*;
import org.mybatis.spring.annotation.MapperScan;

import java.util.List;
import java.util.Map;

@Mapper
public interface PersonMapper {

    @Select("select * from person where id= #{id}")
    Person findPerson(long id);

    @Insert("insert into person(name,age,createtime,updatetime) values(#{name},#{age},#{createtime},#{updatetime})")
    /**
     * 这句用来配置需要主键id，在service里通过getid来获取
     */
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    int insertPerson(Person person);

    @Update("update person set name=#{name},age=#{age},updatetime=#{updatetime} where id=#{id}")
    int updatePerson(Person person);

    @Select("select * from person where name=#{name}")
    Person findPersonByName(@Param("name") String name);

    /**
     * 多表联合查询+模糊查询 like用法
     * @param name 如 "%周%"
     * @return
     */
    //@Select("SELECT a.*,b.sname,b.sdept from person a,cosumers b where a.id = b.pid and a.name like concat('%',#{name},'%')")
    @Select("SELECT a.*,b.sname,b.sdept from person a,cosumers b where a.id = b.pid and a.name like #{name}")
    List<Map> getByParmsMap(String name);

    /**
     * 如果2张表有相同的字段，那么入map时会被覆盖，所以查询时不要用*，要指定列并且如果有相同的列，必须用别名as做区分
     * @param personName 可以加入where条件
     * @return
     */
    @Select("Select a.id,a.name,a.age,a.createtime,a.updatetime,a.state,b.id as bid,b.sname,b.sdept,b.createtime as ctime,b.pid from person a left join cosumers b on a.id=b.pid")
    List<Map> getJoinParamsMap(String personName);

    /**
     * select - in的用法，留意脚本写法，和别的不同
     * @param ids
     * @return
     */
    @Select({
            "<script>",
            "select",
            "*",
            "from person",
            "where id in",
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Map> getBySelectin(@Param("ids") List<Integer> ids);

    /**
     * select - between用法
     * @param stime
     * @param etime
     * @return
     */
    @Select("SELECT a.*,b.sname,b.sdept from person a,cosumers b where a.id = b.pid and a.createtime between #{stime} and #{etime}")
    List<Map> getBySelectBetween(long stime,long etime);

    /**
     * 查询总数
     * @param column 列名 whereflag 操作符(>、<、=...) columnValue 列值条件
     *        没法做到通用，只能根据各种条件统计各种数量了
     * @return
     */
    @Select("<script>"+
            "select count(*) from person"+
            "<where>"+
            "<if test= \"columnValue != null\" >${column} ${whereflag} #{columnValue}</if>"+
            "</where>"+
            "</script>")
    int getCount(String column,String whereflag,String columnValue);

    /**
     * 查找分页--通过service+分页插件完成
     */
    @Select("select * from person")
    List<Person> findAllpersonBypage();
}
